using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace Acms.BllTests
{
    internal class Helper
    {

        internal static void DeleteSqlRow(string tableName, string keyField, int key)
        {
            SqlCommand cmd = new SqlCommand();
            StringBuilder text = new StringBuilder();
            text.AppendLine("BEGIN TRAN ");
            text.AppendFormat("DELETE [acms].[{0}] ", tableName);
            text.AppendFormat("WHERE [{0}] = {1} ", keyField, key);
            text.AppendLine(" ");
            text.AppendLine("IF @@RowCount = 1 COMMIT TRAN ELSE ROLLBACK TRAN");
            cmd.CommandText = text.ToString();
            cmd.Connection = new SqlConnection(Properties.Settings.Default.TestDBConnString);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        internal static void CreateSqlRow(string tableName, System.Collections.Hashtable data, ref int id)
        {
            SqlCommand cmd = new SqlCommand();
            StringBuilder text = new StringBuilder();
            text.AppendFormat("Insert into [acms].[{0}] (", tableName);
            string spacer = "";
            foreach (string key in data.Keys)
            {
                text.AppendFormat("{0}[{1}]", spacer, key);
                spacer = ", ";
            }
            text.Append(") VALUES (");
            
            spacer = "";
            foreach (string key in data.Keys)
            {
                if (data[key] == null)
                {
                    text.AppendFormat("{0}null", spacer);
                }
                else if (data[key] is int)
                {
                    text.AppendFormat("{0}{1}'", spacer, data[key]);
                }
                else if (data[key] is DateTime)
                {
                    text.AppendFormat("{0}'{1:yyyy/MM/dd hh:mm:ss.fff}'", spacer, data[key]);
                }
                else if (data[key] is string)
                {
                    text.AppendFormat("{0}'{1}'", spacer, data[key].ToString());
                }
                else
                {
                    throw new NotImplementedException(string.Format("Data type '{0}' has not been implimented", data[key].GetType().ToString()));
                }
                spacer = ", ";
            }
            text.Append(") SELECT SCOPE_IDENTITY()");

            cmd.CommandText = text.ToString();
            cmd.Connection = new SqlConnection(Properties.Settings.Default.TestDBConnString);

            try
            {
                cmd.Connection.Open();
                id = (int)(decimal)cmd.ExecuteScalar();
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        internal static bool FindSqlRow(string tableName, System.Collections.Hashtable data)
        {
            SqlCommand cmd = new SqlCommand();
            StringBuilder text = new StringBuilder();
            text.AppendFormat("Select Count(*) from [acms].[{0}] where ", tableName);
            string spacer = "";
            foreach (string key in data.Keys)
            {
                text.Append(spacer);
                text.AppendFormat("[{0}] ", key);

                if (data[key] == null)
                {
                    text.Append("is null");
                }
                else if (data[key] is int)
                {
                    text.AppendFormat("= {0}", data[key]);
                }
                else if (data[key] is DateTime)
                {
                    text.AppendFormat("= '{0:yyyy/MM/dd hh:mm:ss.fff}'", data[key]);
                }
                else if (data[key] is string)
                {
                    text.AppendFormat("= '{0}'", data[key].ToString());
                }
                else
                {
                    throw new NotImplementedException(string.Format("Data type '{0}' has not been implimented", data[key].GetType().ToString()));
                }
                spacer = " and ";
            }
            cmd.CommandText = text.ToString();
            cmd.Connection = new SqlConnection(Properties.Settings.Default.TestDBConnString);

            try
            {
                cmd.Connection.Open();
                int count = (int)cmd.ExecuteScalar();
                return count > 0;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        internal static int GetRowCount(string tableName)
        {
            string text = string.Format("select count(*) as Count from [acms].[{0}]", tableName);
            
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = text;
            cmd.Connection = new SqlConnection(Properties.Settings.Default.TestDBConnString);

            int count = 0;
            try
            {
                cmd.Connection.Open();
                count = (int)cmd.ExecuteScalar();
            }
            finally
            {
                cmd.Connection.Close();
            }

            return count;
        }
    }
}
